/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package siswa;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.Statement;
import javax.swing.JFileChooser;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import java.util.Iterator;
import java.util.Vector;
import javax.swing.JOptionPane;


/**
 *
 * @author Rendra
 */
public class ImportDataset extends javax.swing.JInternalFrame {
    
    private File xlsFile;

    /**
     * Creates new form ImportDataset
     */
    public ImportDataset() {
        initComponents();
    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        PathXlsFile = new javax.swing.JTextField();
        BrowseBtn = new javax.swing.JButton();
        ImportBtn = new javax.swing.JButton();
        jLabel1 = new javax.swing.JLabel();
        jLabel2 = new javax.swing.JLabel();
        jLabel3 = new javax.swing.JLabel();

        PathXlsFile.setEnabled(false);

        BrowseBtn.setText("Pilih File ...");
        BrowseBtn.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                BrowseBtnActionPerformed(evt);
            }
        });

        ImportBtn.setText("Proses File");
        ImportBtn.setEnabled(false);
        ImportBtn.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                ImportBtnActionPerformed(evt);
            }
        });

        jLabel1.setText("Silahkan Anda pilih file excel yang mempunyai format yang sesuai dengan ketentuan");

        jLabel2.setText("sehingga proses pemindahan data ke dalam database dapat berjalan normal.");

        jLabel3.setText("Pilih file Microsoft Excel Workbook 1997 - 2003 (.xls) :");

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                        .addComponent(PathXlsFile)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(BrowseBtn))
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(jLabel1)
                            .addComponent(jLabel2)
                            .addComponent(ImportBtn, javax.swing.GroupLayout.PREFERRED_SIZE, 100, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(jLabel3))
                        .addGap(0, 0, Short.MAX_VALUE)))
                .addContainerGap())
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jLabel1)
                .addGap(1, 1, 1)
                .addComponent(jLabel2)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jLabel3)
                .addGap(4, 4, 4)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(PathXlsFile, javax.swing.GroupLayout.PREFERRED_SIZE, 23, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(BrowseBtn))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(ImportBtn)
                .addContainerGap(169, Short.MAX_VALUE))
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void BrowseBtnActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_BrowseBtnActionPerformed
        // TODO add your handling code here:
        JFileChooser chooser = new JFileChooser();
        chooser.setAcceptAllFileFilterUsed(false);
        chooser.addChoosableFileFilter(new OpenFileFilter("xls", "Microsoft Excel 1997 - 2003"));
        int returnVal = chooser.showOpenDialog(null);
        if (returnVal == JFileChooser.APPROVE_OPTION) {
             xlsFile = chooser.getSelectedFile();
             PathXlsFile.setText(xlsFile.toString());
             ImportBtn.setEnabled(true);
        }
    }//GEN-LAST:event_BrowseBtnActionPerformed

    private void ImportBtnActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_ImportBtnActionPerformed
        // TODO add your handling code here:
        Vector cellVectorHolder = new Vector();
        String insert_master = "";
        try {
            Statement stmt = App.conn.createStatement();
            FileInputStream myInput = new FileInputStream(xlsFile);
            POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
            HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
            HSSFSheet mySheet = myWorkBook.getSheetAt(0);
            Iterator rowIter = mySheet.rowIterator();
            while (rowIter.hasNext()) {
                insert_master = "INSERT INTO siswa (tahun_lulus, nama_siswa, n_uan, n_keterampilan, n_kelamin, n_pekerjaan, n_gaji, tujuan_sekolah) VALUES (";
                HSSFRow myRow = (HSSFRow) rowIter.next();
                Iterator cellIter = myRow.cellIterator();
                Vector cellStoreVector = new Vector();
                String cellValue = "";
                while (cellIter.hasNext()) {
                    HSSFCell myCell = (HSSFCell) cellIter.next();
                    cellStoreVector.addElement(myCell);
                    cellValue = myCell.toString();
                    if (cellValue.endsWith(".0"))
                        cellValue = cellValue.substring(0, cellValue.length()-2);
                    insert_master += "'"+cellValue+"',";
                }
                insert_master = insert_master.substring(0, insert_master.length()-1);
                insert_master += ")";
                stmt.executeUpdate(insert_master);
                cellVectorHolder.addElement(cellStoreVector);
            }
            JOptionPane.showMessageDialog(null, "Import Success", "Information", JOptionPane.INFORMATION_MESSAGE);
        } catch (SQLException | IOException e) { System.err.println(e); }
    }//GEN-LAST:event_ImportBtnActionPerformed

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton BrowseBtn;
    private javax.swing.JButton ImportBtn;
    private javax.swing.JTextField PathXlsFile;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    // End of variables declaration//GEN-END:variables
}
